package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.example.demo.bean.Tool;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
* @author lenovo
* @description 针对表【tool】的数据库操作Mapper
* @createDate 2024-09-20 16:02:26
* @Entity com.example.demo.bean.Tool
*/
@Mapper
public interface ToolMapper extends BaseMapper<Tool> {


    @Select("select count(id) as num ,type from code.tool group by type")
// pgsql   @Select("select count(id) as num ,type from code.tool group by type")
    List<Map<String, Object>> statType();

    @Select("select sum(cast(num as UNSIGNED)) as num , supply from code.tool group by supply")
// pgsql   @Select("select sum(cast(num as integer)) as num , supply from code.tool group by supply")
    List<Map<String, Object>> statSupply();

    @Select("select sum(cast(num as UNSIGNED)) as num , type from code.tool group by type")
// pgsql   @Select("select sum(cast(num as integer)) as num , type from code.tool group by type")
    List<Map<String, Object>> statTypeTotal();


/*  pgsql  @Select("select sum(coalesce (cast(b.num as integer ),0))as num , a.list from (select generate_series(current_date -  interval '1 month' , current_date , '1 day'):: date  as list) as  a \n" +
            "left join code.borrow b on a.list = CAST(b.start_time AS date) group by a.list")*/
    @Select("select sum(coalesce(cast(b.num as UNSIGNED),0)) as num, STR_TO_DATE(b.start_time,'%Y-%m-%d') as record from code.borrow b where b.start_time > (current_date - INTERVAL 1 month) group by b.start_time")
    List<Map<String, Object>> statBorrow();


/*  pgsql  @Select("select sum(coalesce (cast(b.num as integer ),0))as num , a.list from (select generate_series(current_date -  interval '1 month' , current_date , '1 day'):: date  as list) as  a \n" +
            "left join code.borrow b on a.list = CAST(b.return_time AS date) group by a.list")*/
    @Select("select sum(coalesce(cast(b.num as UNSIGNED),0)) as num, STR_TO_DATE(b.return_time,'%Y-%m-%d') as record from code.borrow b where b.return_time > (current_date - INTERVAL 1 month) group by b.return_time")
    List<Map<String, Object>> statReturn();


}




